package busticketvendingsystem;
import java.sql.*;
import java.text.*;
public class ConnectDatabase {
	private String url,db,driver,user,pass;
	Connection con;
	ResultSet rst;
	PreparedStatement pstmt;
	Statement stmt;
	public ConnectDatabase(){
		url="jdbc:mysql://localhost:3306/";
		db="BusTicketVending";
		driver="com.mysql.jdbc.Driver";
		user="root";
		pass="root";
		con=null;
		rst=null;
		pstmt=null;
		stmt=null;
	}
	int getNumber_of_rows(String table_name){
		int count=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT COUNT(*) FROM "+table_name);
				while(rst.next()){
					count=rst.getInt(1);
				}
			}catch(SQLException s){
				count=0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return count;
	}
        int getNumber_of_transaction_bydate_bnumber(String date,String bnumber){
		int count=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT COUNT(*) FROM Transaction WHERE Date=? AND Bus_Number=?");
				pstmt.setString(1,date);
                                pstmt.setString(2,bnumber);
				rst=pstmt.executeQuery();
                                while(rst.next()){
					count=rst.getInt(1);
				}
			}catch(SQLException s){
				count=0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return count;
        }
        int getNumber_of_transaction_per_day_bydate(String date){
		int count=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT COUNT(*) FROM Transaction_Per_day WHERE Date=?");
				pstmt.setString(1,date);
				rst=pstmt.executeQuery();
                                while(rst.next()){
					count=rst.getInt(1);
				}
			}catch(SQLException s){
				count=0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return count;
        }
        int getNumber_of_transaction_per_day_bynumber(String bnumber){
		int count=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT COUNT(*) FROM Transaction_Per_day WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
                                while(rst.next()){
					count=rst.getInt(1);
				}
			}catch(SQLException s){
				count=0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
                return count;
        }
	String[] getStoppage_name(){
                int i=0;
		String stp[]=new String [getNumber_of_rows("Stoppage")];
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Stoppage_Name FROM Stoppage ORDER BY Distance ASC");
				while(rst.next()){
					stp[i++]= rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return stp;
	}
        String[] getStoppage_name_back(){
                int i=0;
		String stp[]=new String [getNumber_of_rows("Stoppage")];
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Stoppage_Name FROM Stoppage ORDER BY Distance DESC");
				while(rst.next()){
					stp[i++]= rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return stp;
	}
	float getDistance(String sname){
		float dis=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Distance FROM Stoppage WHERE Stoppage_Name=?");
				pstmt.setString(1,sname);
				rst=pstmt.executeQuery();
				while(rst.next()){
					dis= rst.getFloat(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return dis;
	}
        float getAbsoluteDistance(String sname1,String sname2){
            float dis=Math.abs(getDistance(sname1)-getDistance(sname2));
            return dis;
        }
	String[] getBus_number_byUpSchedule(){
		String bsn[]=new String [getNumber_of_rows("Bus")];
                int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Bus_Number FROM Bus ORDER BY Up_Schedule ASC");
				while(rst.next()){
					bsn[i++]= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return bsn;
	}
	String[] getBus_number_byDownSchedule(){
		String bsn[]=new String [getNumber_of_rows("Bus")];
                int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Bus_Number FROM Bus ORDER BY Down_Schedule ASC");
				while(rst.next()){
					bsn[i++]= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return bsn;
	}
        String getBus_numberbyUp(String time){
            String bnumber="";
            int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Bus_Number FROM Bus WHERE Up_Schedule=?");
                                pstmt.setString(1,time);
                                rst=pstmt.executeQuery();
				while(rst.next()){
					bnumber= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return bnumber;
        }
        String getBus_numberbyDown(String time){
            String bnumber="";
            int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Bus_Number FROM Bus WHERE Down_Schedule=?");
                                pstmt.setString(1,time);
                                rst=pstmt.executeQuery();
				while(rst.next()){
					bnumber= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return bnumber;
        }
	int getSeats(String bnumber){
		int seats=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Seats FROM Bus WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					seats= rst.getInt(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return seats;
	}
	String getUpSchedule(String bnumber){
		String time="";
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Up_Schedule FROM Bus WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					time= rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return time;
	}
	String getDownSchedule(String bnumber){
		String time="";
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Down_Schedule FROM Bus WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					time= rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return time;
	}
        String[] getUpSchedule(){
            String up[]=new String [getNumber_of_rows("Bus")];
            int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Up_Schedule FROM Bus ORDER BY Up_Schedule ASC");
				while(rst.next()){
					up[i++]= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return up;
        }
        String[] getDownSchedule(){
            String down[]=new String [getNumber_of_rows("Bus")];
            int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				rst=stmt.executeQuery("SELECT Down_Schedule FROM Bus ORDER BY Down_Schedule ASC");
				while(rst.next()){
					down[i++]= rst.getString(1);
					
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return down;
        }
	boolean set_Transaction(String id,String bnumber,String start,String end,float amount,String date){
		boolean type=false;
		try{
			Class.forName(driver);
			con = DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("INSERT INTO Transaction VALUES(?,?,?,?,?,?)");
		        pstmt.setString(1,id);
		        pstmt.setString(2,bnumber);
		        pstmt.setString(3,start);
		        pstmt.setString(4,end);
		        pstmt.setFloat(5,amount);
		        pstmt.setString(6,date);
		        pstmt.executeUpdate();
		        type=true;
                        }
			catch (SQLException s){
				type=false;
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		return type;
	}
	boolean set_Transaction_per_day(String date,String bnumber,int up,int down,float amount){
		boolean type=false,set=false;
		int upt=0,dwt=0;
		float tamnt=0;
		try{
			Class.forName(driver);
			con = DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Transaction_Per_Day WHERE Date=? AND Bus_Number=?");
				pstmt.setString(1,date);
				pstmt.setString(2,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					set=true;
					upt=rst.getInt(3);
					dwt=rst.getInt(4);
					tamnt=rst.getFloat(5);
				}
				if(set){
					pstmt=con.prepareStatement("UPDATE Transaction_Per_Day SET Up_Tickets_Sold=?,Down_Tickets_Sold=?,Total_Income=? WHERE Date=? AND Bus_Number=?");
					pstmt.setInt(1,upt+up);
					pstmt.setInt(2,dwt+down);
					pstmt.setFloat(3,tamnt+amount);
					pstmt.setString(4,date);
					pstmt.setString(5,bnumber);
					pstmt.executeUpdate();
					type=true;
				}
				else{
					pstmt=con.prepareStatement("INSERT INTO Transaction_Per_Day VALUES(?,?,?,?,?)");
					pstmt.setString(1,date);
					pstmt.setString(2,bnumber);
					pstmt.setInt(3,up);
					pstmt.setInt(4,down);
		        	pstmt.setFloat(5,amount);
		        	pstmt.executeUpdate();
		        	type=true;
				}
			}
			catch (SQLException s){
				type=false;
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		return type;
	}
	boolean insertStoppage(String id,String sname,float distance){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("INSERT INTO Stoppage VALUES(?,?,?)");
				pstmt.setString(1,id);
				pstmt.setString(2,sname);
				pstmt.setFloat(3,distance);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	String[] getStoppage(String sname){
		String stoppage[]=new String [3];
                int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Stoppage WHERE Stoppage_Name=?");
				pstmt.setString(1,sname);
				rst=pstmt.executeQuery();
				while(rst.next()){
					stoppage[i++]= rst.getString(1);
					stoppage[i++]= rst.getString(2);
					stoppage[i++]= rst.getString(3);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return stoppage;
	}
        boolean isStoppage(String sname){
            boolean type=false;
            try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Stoppage WHERE Stoppage_Name=?");
				pstmt.setString(1,sname);
				rst=pstmt.executeQuery();
				while(rst.next()){
					type=true;
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
                return type;
        }
	boolean updateStoppage(String id,String sname,float distance){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("UPDATE Stoppage SET Stoppage_Name=?,Distance=? WHERE Stoppage_Id=?");
				pstmt.setString(3,id);
				pstmt.setString(1,sname);
				pstmt.setFloat(2,distance);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	boolean deleteStoppage(String id){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("DELETE FROM Stoppage WHERE Stoppage_Id=?");
				pstmt.setString(1,id);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	boolean insertBus(String bnumber,String up,String down,int seats){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("INSERT INTO Bus VALUES(?,?,?,?)");
				pstmt.setString(1,bnumber);
				pstmt.setString(2,up);
				pstmt.setString(3,down);
				pstmt.setInt(4,seats);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	String[] getBus(String bnumber){
		String bus[]=new String[4];
                int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Bus WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					bus[i++]= rst.getString(1);
					bus[i++]= rst.getString(2);
					bus[i++]= rst.getString(3);
					bus[i++]= rst.getString(4);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return bus;
	}
	boolean updateBus(String bnumber,String up,String down,int seats){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("UPDATE Bus SET Up_Schedule=?,Down_Schedule=?,Seats=? WHERE Bus_Number=?");
				pstmt.setString(4,bnumber);
				pstmt.setString(1,up);
				pstmt.setString(2,down);
				pstmt.setInt(3,seats);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	boolean deleteBus(String bnumber){
		boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("DELETE FROM Bus WHERE Bus_Number=?");
				pstmt.setString(1,bnumber);
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
	}
	String[] getTransaction_date_bnumberwise(String date,String bnumber){
		String tran[]=new String[getNumber_of_transaction_bydate_bnumber(date,bnumber)];
		int i=0;
                try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Transaction WHERE Date=? AND Bus_Number=?");
				pstmt.setString(1,date);
                                pstmt.setString(2,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					tran[i++]= rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return tran;
	}
	String[] getTransaction_per_day_datewise(String date){
		String tran[]=new String[getNumber_of_transaction_per_day_bydate(date)];
		int i=0;
                try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Transaction_Per_Day WHERE Date=?");
				pstmt.setString(1,date);
				rst=pstmt.executeQuery();
				while(rst.next()){
					tran[i]= rst.getString(1)+",";
					tran[i]+= rst.getString(2)+",";
					tran[i]+= rst.getString(3)+",";
					tran[i]+= rst.getString(4)+",";
					tran[i]+= rst.getString(5)+",";
                                        i++;
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return tran;
	}
        String[] getTransaction_per_day_busnumberwise(String bnumber){
		int i=0;
                String tran[]=new String[getNumber_of_transaction_per_day_bynumber(bnumber)];
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT * FROM Transaction_Per_Day WHERE Bus_Number=? ORDER BY Date DESC");
				pstmt.setString(1,bnumber);
				rst=pstmt.executeQuery();
				while(rst.next()){
					tran[i]= rst.getString(2)+",";
					tran[i]+= rst.getString(1)+",";
					tran[i]+= rst.getString(3)+",";
					tran[i]+= rst.getString(4)+",";
					tran[i]+= rst.getString(5)+",";
                                        i++;
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
   
		return tran;
	}
        boolean setPassword(String password){
            boolean type=false;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("UPDATE Other SET Data=? WHERE Item=?");
				pstmt.setString(1,password);
				pstmt.setString(2,"Password");
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
        }
        String getPassword(){
            String password="";
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("SELECT * FROM Other WHERE Item=?");
				pstmt.setString(1,"Password");
				rst=pstmt.executeQuery();
				while(rst.next()){
					password= rst.getString(2);
                                        
				}
			}catch(SQLException s){
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return password;
        }
	boolean setFare(float minfare,float unitfare){
            boolean type=false;
            String minf=Float.toString(minfare);
            String unitf=Float.toString(unitfare);
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("UPDATE Other SET Data=? WHERE Item=?");
				pstmt.setString(1,minf);
				pstmt.setString(2,"Minimum fare");
				pstmt.executeUpdate();
                                pstmt=con.prepareStatement("UPDATE Other SET Data=? WHERE Item=?");
				pstmt.setString(1,unitf);
				pstmt.setString(2,"Fare per km");
				pstmt.executeUpdate();
				type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
        }
        String[] getFare(){
            String fare[]=new String [2];
            int i=0;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				pstmt=con.prepareStatement("SELECT * FROM Other WHERE Item=?");
				pstmt.setString(1,"Minimum fare");
				rst=pstmt.executeQuery();
				while(rst.next()){
					fare[i++]= rst.getString(2);
				}
                                pstmt=con.prepareStatement("SELECT * FROM Other WHERE Item=?");
				pstmt.setString(1,"Fare per km");
				rst=pstmt.executeQuery();
				while(rst.next()){
					fare[i++]= rst.getString(2);
				}
			}catch(SQLException s){
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return fare;
        }
        boolean sortStoppage(){
            boolean type=false;
            int row=getNumber_of_rows("Stoppage");
            String stps[]=getStoppage_name();
            try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				for(int i=0;i<row;i++){
                                    pstmt=con.prepareStatement("UPDATE Stoppage SET Stoppage_Id=? WHERE Stoppage_Name=?");
                                    DecimalFormat threeDigits=new DecimalFormat ("000");
                                    pstmt.setString(1,"S"+threeDigits.format(i+1));
                                    pstmt.setString(2,stps[i]);
                                    pstmt.executeUpdate();
                                    
                                }
                                type=true;
			}catch(SQLException s){
				type=false;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
		return type;
        }
        int getStoppage_id(String sname){
            int id;
            String sid="00";
            try{
			Class.forName(driver);
			con=DriverManager.getConnection(url+db,user,pass);
			try{
				stmt=con.createStatement();
				pstmt=con.prepareStatement("SELECT Stoppage_Id FROM Stoppage WHERE Stoppage_Name=?");
				pstmt.setString(1,sname);
				rst=pstmt.executeQuery();
				while(rst.next()){
					sid=rst.getString(1);
				}
			}catch(SQLException s){}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(con!=null){
					con.close();
				}
			}catch(SQLException s){}
		}
                int len=sid.length();
                sid=sid.substring(1,len);
                id=Integer.parseInt(sid);
                
                return id;
        } 
}
